package com.ymt.fmd.platform.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.logging.Logger;

import com.ymt.fmd.appboot.prop.FmdProperties;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ymt.fmd.platform.dao.mysql.CustomSQLDao;
import com.ymt.fmd.platform.dao.mysql.PDataMMapper;
import com.ymt.fmd.platform.dao.mysql.PDataSMapper;
import com.ymt.fmd.platform.model.PDataM;
import com.ymt.fmd.platform.model.PDataS;
import com.ymt.fmd.util.DateUtil;
import com.ymt.fmd.util.DhxUtil;

@Service
@EnableConfigurationProperties(value = {FmdProperties.class})
public class BoDataService {

	/**
	 * logger
	 */
	private Logger logger = Logger.getLogger(BoDataService.class.getName());

	@Autowired
	private FmdProperties fmdProperties;
	
	/**
	 * custom sql dao
	 */
	@Autowired
	private CustomSQLDao csDao;
	
	@Autowired
	private PDataMMapper dmDao;
	
	@Autowired
	private PDataSMapper dsDao;
	
	/**
	 * get single bo
	 * @param tsId
	 * @param models
	 * @param timezoneoffset
	 * @return
	 * @throws Exception
	 */
	public String get(String tenantId, String tsId, String models, int timezoneoffset) throws Exception {
		boolean JSONDATA_ENABLED = Boolean.parseBoolean(fmdProperties.getJsondataEnabled());
		boolean MONGODATA_BIZ = Boolean.parseBoolean(fmdProperties.getMongodataBiz());
		String DB_RESOLVER = fmdProperties.getBoDbresolver();
		logger.finer("get fmd.jsondata.enabled="+JSONDATA_ENABLED);
		String[] mNames = models.split(";");
		JSONObject rtn = new JSONObject();
		BoDbResolver br = null;
		if (!JSONDATA_ENABLED) {
			br = (BoDbResolver)Class.forName(DB_RESOLVER).newInstance();
		}
		for (String m : mNames) {
			if (JSONDATA_ENABLED) {
				PDataM data = new PDataM();
				if (tenantId!=null && StringUtils.isNumeric(tenantId)) {
					data.setTenantId(Integer.parseInt(tenantId));
				}
				data.setPiEid(tsId);
				data = dmDao.selectByPrimaryKey(tenantId, tsId, m);
				if (data!=null) {
					JSONObject json = JSONObject.parseObject(data.getBizData());
					/*JSONObject map = new JSONObject();
					for (Object ko : json.keySet()) {
						JSONObject attr = json.getJSONObject((String)ko);
						Object val = attr.get("value");
						if (val!=null && !(val instanceof JSONNull)) {
							map.put((String)ko, val);
						}
					}*/
					rtn.put(m, json);
				} else {
					rtn.put(m, new JSONObject());
				}
			} else {
				String sql = "select * from "+m+" where ts_id='"+tsId+"'";
				Map<String, ?> rs = csDao.selectOne(sql);
				JSONObject map = br.transformResultMap(rs, timezoneoffset);
				rtn.put(m, map);
			}
		}
		logger.finer("get rtn="+rtn.toString());
		return rtn.toString();
	}

	/**
	 * save single bo
	 * @param tsId
	 * @param data
	 * @param timezoneoffset
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public String save(String tenantId, String tsId, String data, int timezoneoffset) throws Exception {
		boolean JSONDATA_ENABLED = Boolean.parseBoolean(fmdProperties.getJsondataEnabled());
		boolean MONGODATA_BIZ = Boolean.parseBoolean(fmdProperties.getMongodataBiz());
		String DB_RESOLVER = fmdProperties.getBoDbresolver();
		BoDbResolver br = null;
		if (!JSONDATA_ENABLED) {
			br = (BoDbResolver)Class.forName(DB_RESOLVER).newInstance();
		}
		JSONObject jo = JSONObject.parseObject(data);
		int rs = 0;
		for (String m : (Set<String>)jo.keySet()) {
			JSONObject attrs = jo.getJSONObject(m);
			if (JSONDATA_ENABLED) {
				PDataM dm = dmDao.selectByPrimaryKey(tenantId, tsId, m);
				JSONObject json = new JSONObject();
				this.setValueJSON(json, attrs, timezoneoffset);
				if (dm==null) {
					dm = new PDataM();
					if (tenantId!=null && StringUtils.isNumeric(tenantId)) {
						dm.setTenantId(Integer.parseInt(tenantId));
					}
					dm.setPiEid(tsId);
					dm.setModel(m);
					dm.setBizData(json.toString());
					rs += dmDao.insertSelective(dm);
				} else {
					dm.setBizData(json.toString());
					rs += dmDao.updateByPrimaryKeySelective(dm);
				}
			} else {
				Map<String, ?> bomap = csDao.selectOne("select * from "+m+" where TS_ID='"+tsId+"'");
				if (bomap==null) {
					String sql = br.getInsertSql(tsId, m, attrs, timezoneoffset);
					rs += csDao.insert(sql);
				} else {
					String sql = br.getUpdateSql(tsId, m, attrs, timezoneoffset);
					rs += csDao.update(sql);
				}
			}
		}
		return "{\"success\":"+rs+"}";
	}
	
	/**
	 * query bo data in dhx format with paging
	 * @param tenantId
	 * @param tsId
	 * @param paramStr
	 * @param lang
	 * @return
	 * @throws Exception 
	 */
	public String queryDhx(String tenantId, String tsId, String paramStr, String lang) throws Exception {
		boolean JSONDATA_ENABLED = Boolean.parseBoolean(fmdProperties.getJsondataEnabled());
		boolean MONGODATA_BIZ = Boolean.parseBoolean(fmdProperties.getMongodataBiz());
		String DB_RESOLVER = fmdProperties.getBoDbresolver();

		JSONObject param = JSONObject.parseObject(paramStr);
		String m = param.getString("model");
		JSONArray columns = param.getJSONArray("columns");
		int timezoneOffset = param.getIntValue("timezoneOffset");
		String orderby = DhxUtil.parseGridOrderBy(param.getString("ordercolumns"));
		String[] cols = new String[]{};
		cols = (String[]) columns.toArray(cols);
		int fromRow = param.getIntValue("fromRow");
		int toRow = param.getIntValue("toRow");
		//BoDbResolver br = (BoDbResolver)Class.forName(DB_RESOLVER).newInstance();
		String sql = "select * from "+m+" where ts_id='"+tsId+"'";
		//count
		int total = Integer.parseInt(csDao.selectCount(sql).get(0).get("COUNT").toString());
		//query
		if (orderby!=null && orderby.length()>0) {
			sql += orderby;
		}
		List<Map<String,?>> rs = csDao.pagingSelect(sql, fromRow-1, toRow - fromRow + 1);
		List<Map<String,?>> newRs = new ArrayList<Map<String,?>>();
		if (rs!=null && rs.size()>0) {
			BoDbResolver br = (BoDbResolver)Class.forName(DB_RESOLVER).newInstance();
			for (Map<String, ?> row : rs) {
				row = br.transformResultMap(row, timezoneOffset);
				newRs.add(row);
			}
		}
		String rtn= DhxUtil.toGridJsonStr(newRs, 
				cols, fromRow, total, timezoneOffset, lang);
		logger.finer("queryDhx rtn="+rtn);
		return rtn;
	}
	
	/**
	 * query bo data in dhx format without paging
	 * @param tenantId
	 * @param tsId
	 * @param paramStr
	 * @param lang
	 * @return
	 */
	public String queryAllDhx(String tenantId, String tsId, String paramStr, String lang) throws Exception {
		boolean JSONDATA_ENABLED = Boolean.parseBoolean(fmdProperties.getJsondataEnabled());
		boolean MONGODATA_BIZ = Boolean.parseBoolean(fmdProperties.getMongodataBiz());
		String DB_RESOLVER = fmdProperties.getBoDbresolver();

		JSONObject param = JSONObject.parseObject(paramStr);
		String m = param.getString("model");
		JSONArray columns = param.getJSONArray("columns");
		int timezoneOffset = param.getIntValue("timezoneOffset");
		String[] cols = new String[]{};
		cols = (String[]) columns.toArray(cols);
		String rtn = null;
		if (JSONDATA_ENABLED) {
			PDataS key = new PDataS();
			if (tenantId!=null && StringUtils.isNumeric(tenantId)) {
				key.setTenantId(Integer.parseInt(tenantId));
			}
			key.setPiEid(tsId);
			key.setModel(m);
			List<PDataS> rs = dsDao.selectByObj(key);
			List<Map<String,?>> newRs = new ArrayList<Map<String,?>>();
			int total = 0;
			if (rs!=null && rs.size()>0) {
				total = rs.size();
				for (PDataS ds : rs) {
					JSONObject map = JSONObject.parseObject(ds.getBizData());
					newRs.add(map);
				}
			}
			rtn= DhxUtil.toGridJsonStr(newRs, 
					cols, 1, total, timezoneOffset, lang);
		} else {
			String sql = "select * from "+m+" where ts_id='"+tsId+"'";
			//count
			int total = Integer.parseInt(csDao.selectCount(sql).get(0).get("COUNT").toString());
			//query
			String orderby = DhxUtil.parseGridOrderBy(param.getString("ordercolumns"));
			if (orderby!=null && orderby.length()>0) {
				sql += orderby;
			}
			List<Map<String,?>> rs = csDao.selectAll(sql);
			List<Map<String,?>> newRs = new ArrayList<Map<String,?>>();
			if (rs!=null && rs.size()>0) {
				BoDbResolver br = (BoDbResolver)Class.forName(DB_RESOLVER).newInstance();
				for (Map<String, ?> row : rs) {
					row = br.transformResultMap(row, timezoneOffset);
					newRs.add(row);
				}
			}
			rtn= DhxUtil.toGridJsonStr(newRs, 
					cols, 1, total, timezoneOffset, lang);
		}
		logger.finer("queryDhx rtn="+rtn);
		return rtn;
	}
	
	/**
	 * save bo data in dhx format
	 * @param tenantId
	 * @param tsId
	 * @param paramStr
	 * @param lang
	 * @return
	 * @throws Exception 
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public String saveAllDhx(String tenantId, String tsId, String paramStr, String lang) throws Exception {
		boolean JSONDATA_ENABLED = Boolean.parseBoolean(fmdProperties.getJsondataEnabled());
		boolean MONGODATA_BIZ = Boolean.parseBoolean(fmdProperties.getMongodataBiz());
		String DB_RESOLVER = fmdProperties.getBoDbresolver();

		JSONObject paramObj = JSONObject.parseObject(paramStr);
		JSONObject models = paramObj.getJSONObject("models");
		String modelName = models.getString("model");
		JSONObject attrs = models.getJSONObject("modelAttrs");
		JSONObject colIndex = paramObj.getJSONObject("colIndex");
		JSONArray rows = paramObj.getJSONArray("rows");
		int timezoneOffset = paramObj.getIntValue("timezoneOffset");
		int rs = 0;
		BoDbResolver br = null;
		if (JSONDATA_ENABLED) {
			PDataS key = new PDataS();
			if (tenantId!=null && StringUtils.isNumeric(tenantId)) {
				key.setTenantId(Integer.parseInt(tenantId));
			}
			key.setPiEid(tsId);
			key.setModel(modelName);
			rs += dsDao.deleteByObj(key);
			for (int i=0; i<rows.size(); i++) {
				JSONArray values = rows.getJSONArray(i);
				logger.finest("values="+values);
				Set<Map.Entry<String, Object>> set = attrs.entrySet();
				for (Map.Entry e : set) {
					String colName = (String)e.getKey();
					JSONObject colObj = (JSONObject)e.getValue();
					colObj.remove("value");
					if (colIndex.containsKey(colName)) {
						colObj.put("value", values.get(colIndex.getIntValue(colName)));
					} else {
						continue;
					}
				}
				JSONObject json = new JSONObject();
				this.setValueJSON(json, attrs, timezoneOffset);
				PDataS ds = new PDataS();
				if (tenantId!=null && StringUtils.isNumeric(tenantId)) {
					ds.setTenantId(Integer.parseInt(tenantId));
				}
				ds.setPiEid(tsId);
				ds.setModel(modelName);
				ds.setItemNo(i+1);
				ds.setBizData(json.toString());
				rs += dsDao.insert(ds);
				logger.finer("inserted 1 PDataS.");
			}
		} else {
			br = (BoDbResolver)Class.forName(DB_RESOLVER).newInstance();
			//delete all first
			String delSql = "delete from "+modelName+" where TS_ID='"+tsId+"'";
			csDao.delete(delSql);
			//insert all
			for (int i=0; i<rows.size(); i++) {
				JSONArray values = rows.getJSONArray(i);
				logger.finest("values="+values);
				Set<Map.Entry<String, Object>> set = attrs.entrySet();
				for (Map.Entry e : set) {
					String colName = (String)e.getKey();
					JSONObject colObj = (JSONObject)e.getValue();
					colObj.remove("value");
					if (colIndex.containsKey(colName)) {
						colObj.put("value", values.get(colIndex.getIntValue(colName)));
					} else {
						continue;
					}
				}
				String sql = br.getInsertSql(tsId, modelName, attrs, timezoneOffset);
				rs += csDao.insert(sql);
				logger.finer("insert sql: "+sql);
			}
		}
		
		return "{\"success\":"+rs+"}";
	}
	
	/**
	 * query bo data in bootstrap format with paging
	 * @param piId
	 * @param paramStr
	 * @param lang
	 * @return
	 */
	public String queryBt(String tenantId, String piId, String paramStr, String lang) {
		JSONObject param = JSONObject.parseObject(paramStr);
		String m = param.getString("model");
		JSONArray columns = param.getJSONArray("columns");
		JSONArray searchCols = param.getJSONArray("searchCols");
		String searchStr = param.getString("searchStr");
		int timezoneOffset = param.getIntValue("timezoneOffset");
		String orderby = DhxUtil.parseGridOrderBy(param.getString("ordercolumns"));
		String[] cols = new String[]{};
		cols = (String[]) columns.toArray(cols);
		int fromRow = param.getIntValue("fromRow");
		int toRow = param.getIntValue("toRow");
		//BoDbResolver br = (BoDbResolver)Class.forName(DB_RESOLVER).newInstance();
		String sql = "select * from "+m+" where 0=0 ";
		if (piId!=null && piId.length()>0) {
			sql += " and ts_id='"+piId+"' ";
		}
		if (searchCols!=null && searchCols.size()>0 && searchStr!=null && searchStr.length()>0) {
			sql += "and (";
			for (int i=0; i<searchCols.size(); i++) {
				if (i>0) sql += " or ";
				sql += searchCols.getString(i)+" like '%"+searchStr+"%' ";
			}
			sql += ")";
		}
		//count
		int total = Integer.parseInt(csDao.selectCount(sql).get(0).get("COUNT").toString());
		//query
		if (orderby!=null && orderby.length()>0) {
			sql += orderby;
		}
		List<Map<String,?>> rs = csDao.pagingSelect(sql, fromRow-1, toRow - fromRow + 1);
		String rtn= DhxUtil.toGridJsonBtStr(rs, 
				cols, fromRow, total, timezoneOffset, lang);
		logger.finer("queryBt rtn="+rtn);
		return rtn;
	}
	
	private void setValueJSON(JSONObject map, JSONObject attrs, int timezoneoffset) {
		for (String attr : (Set<String>)attrs.keySet()) {
			if (attr.equalsIgnoreCase("TS_ID") || attr.equalsIgnoreCase("ITEM_NO_")) {
				continue;
			}
			JSONObject ao = attrs.getJSONObject(attr);
			String v = null;
			String type = ao.getString("type");
			Object val = ao.get("value");
			String def = ao.getString("default");
			if ("string".equalsIgnoreCase(type) || "text".equalsIgnoreCase(type)) {
				if (val==null || val.toString().length()==0) {
					val = def;
					//if (val==null) val = "";
				}
				if (val instanceof String) {
					v = (String)val;
				} else {
					v = val.toString();
				}
			} else if ("int".equalsIgnoreCase(type) || "number".equalsIgnoreCase(type)) {
				if (val==null || val.toString().length()==0) {
					if (def==null ||def.length()==0) {
						continue;
					}
					val = def;
				}
				v = val.toString();
			} else if ("datetime".equalsIgnoreCase(type)) {
				if(val==null){
					v = null;
				}else{
					v = val.toString().length()==10? val.toString()+" 00:00:00" : val.toString();
					
					String sval = val.toString();
					String pattern = "yyyy-MM-dd HH:mm:ss";
					if(sval.length()==10 && sval.indexOf("-")>0){
						pattern = "yyyy-MM-dd";
					}else if(sval.length()==8 && sval.indexOf(":")>0){
						pattern = "HH:mm:ss";
					}else if(sval.length()==16){
						pattern = "yyyy-MM-dd HH:mm";
					}else if(sval.length()==19){
						pattern = "yyyy-MM-dd HH:mm:ss";
					}
					
					//写入库的时候 - timezoneoffset； 读取的时候 + timezoneoffset；
					String nval = DateUtil.formatDate(DateUtil.formatStrToDate(sval, pattern), pattern, 0-timezoneoffset);
					v = nval;
				}
				
			}
			map.put(attr.toUpperCase(), v);
		}
	}
	
}
